package Homework.Top2.SubjectDao;


import Homework.Top1.DBUtil;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;


public class SubjectDao {
    //插入学科信息并返回主键
    public int add(Subject s){
        Connection connection = null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            connection = DBUtil.getConnection();
            ps=connection.prepareStatement("INSERT INTO `s_subject`( `subjectname`, `classhour`, `gradeid`) VALUES (?, ?, ?);",com.mysql.jdbc.Statement.RETURN_GENERATED_KEYS);
            ps.setString(1,s.getSubjectname());
            ps.setInt(2,s.getClasshour());
            ps.setLong(3,s.getGradeid());
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                int id = rs.getInt(1);
                return id;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,connection);
        }
        return -1;
    }
    //修改学科信息
    public int update(Subject s){
        Connection connection = null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            connection = DBUtil.getConnection();
            ps=connection.prepareStatement("UPDATE `myschool`.`s_subject` SET `subjectname` = ?, `classhour` = ? ,`gradeid` = ? WHERE `id` = ?;");
            ps.setString(1,s.getSubjectname());
            ps.setInt(2,s.getClasshour());
            ps.setLong(3,s.getGradeid());
            ps.setLong(4,s.getId());
            int num=ps.executeUpdate();
            return num;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,connection);
        }
        return 0   ;
    }
    //删除学科信息
    public int del(long id){
        Connection connection = null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            connection = DBUtil.getConnection();
            ps=connection.prepareStatement("DELETE FROM `s_subject` WHERE `id` = ?");
            ps.setLong(1,id);
            int num=ps.executeUpdate();
            return num;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,connection);
        }
        return 0;
    }
    //查询全部课程信息
    public List<Subject> searchAll(){
        List<Subject>datas=new LinkedList<>();
        Connection connection = null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            connection = DBUtil.getConnection();
            ps=connection.prepareStatement("select * from s_subject order by id desc");
            rs=ps.executeQuery();
            while (rs.next()){
                Subject su=new Subject();
                su.setId(rs.getLong("id"));
                su.setSubjectname(rs.getString("subjectname"));
                su.setClasshour(rs.getInt("classhour"));
                su.setGradeid(rs.getLong("gradeid"));
                datas.add(su);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,connection);
        }
        return datas;
    }
    //根据主键找单个对象的查询方法
    public Subject searchOne(long id){
        Connection connection = null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            connection = DBUtil.getConnection();
            ps=connection.prepareStatement("select * from s_subject where id=?");
            ps.setLong(1,id);
            rs=ps.executeQuery();
            if (rs.next()){
                Subject su=new Subject();
                su.setId(rs.getLong("id"));
                su.setSubjectname(rs.getString("subjectname"));
                su.setClasshour(rs.getInt("classhour"));
                su.setGradeid(rs.getLong("gradeid"));
                return su;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,connection);
        }
        return null;
    }

}
